package com.hwg.emp.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.hwg.emp.common.dto.KeysQueryDto;
import com.hwg.emp.common.util.DBUtil;
import com.hwg.emp.dao.AskoffDAO;
import com.hwg.emp.dto.AskoffDto;
import com.hwg.emp.dto.PageBean;
import com.hwg.emp.model.AskoffMD;
import com.hwg.emp.model.EmployeeMD;
import com.hwg.emp.model.OvertimeMD;


public class AskoffDAOImpl implements AskoffDAO{

	@Override
	public int insert(AskoffMD t) throws Exception {
		return 0;
		
	}

	@Override
	public int delete(String uk, String ukValue) throws Exception {
		return 0;
		
	}

	@Override
	public int update(AskoffMD t) throws Exception {
		return 0;
		// TODO Auto-generated method stub
		
	}

	@Override
	public AskoffMD select(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return null;
	}

	@Override
	public boolean exist(String uk, String ukValue) throws Exception {
		// TODO Auto-generated method stub
		return false;
	}


	@Override
	public int selectEmpAskoff(String deptId) throws Exception {
		// TODO Auto-generated method stub
		int account=0;
		StringBuilder str=new StringBuilder();
		str.append("SELECT COUNT( * ) scount ");
		str.append("FROM emp_employee,rcd_askoff,");
		str.append("sys_user WHERE rcd_askoff.emp_id=emp_employee.id AND ");
		str.append("sys_user.id=emp_employee.id AND emp_employee.dept_id=? ");
		//String sql="SELECT COUNT( * ) scount FROM rcd_askoff,sys_user WHERE rcd_askoff.emp_id=sys_user.id";
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(str.toString());
		pst.setString(1, deptId);
		ResultSet rs=pst.executeQuery();
		if(rs.next()){
			account=rs.getInt("scount");
		}
		return account;
	}


	@Override
	public boolean addEmpAskoff(AskoffDto askoffDto) throws Exception {
		// TODO Auto-generated method stub
		boolean flag=false;
		String sql="INSERT INTO rcd_askoff (id,startdate,enddate,days,reason,emp_id) VALUES (?,?,?,?,?,?)"; 
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setString(1, askoffDto.getId());
		pst.setDate(2, askoffDto.getStartDate());
		pst.setDate(3, askoffDto.getEndDate());
		pst.setInt(4, askoffDto.getDays());
		pst.setString(5, askoffDto.getReason());
		pst.setString(6, askoffDto.getUser().getId());
		int i=pst.executeUpdate();
		if(i>0){
			flag=true;
		}
		return flag;
	}

	@Override
	public List<AskoffDto> selectEmpAskoff(String deptId, PageBean pageBean) throws Exception {
		// TODO Auto-generated method stub
		List<AskoffDto> askoffDtos=new ArrayList<AskoffDto>();
		Connection con=DBUtil.getConnection();
		StringBuilder str=new StringBuilder();
		str.append("SELECT rcd_askoff.id,sys_user.`name`,rcd_askoff.startdate,rcd_askoff.enddate,");
		str.append("rcd_askoff.days,rcd_askoff.reason FROM emp_employee,rcd_askoff,");
		str.append("sys_user WHERE rcd_askoff.emp_id=emp_employee.id AND ");
		str.append("sys_user.id=emp_employee.id AND emp_employee.dept_id=? ");
		str.append("limit ?,?");
		PreparedStatement pst=con.prepareStatement(str.toString());
		pst.setString(1, deptId);
		pst.setInt(2,pageBean.getStart());
		pst.setInt(3, pageBean.getPrecord());
		ResultSet rs=pst.executeQuery();
		while(rs.next()){
			AskoffDto askoffDto=new AskoffDto();
			askoffDto.setId(rs.getString("id"));
			askoffDto.setName(rs.getString("name"));
			askoffDto.setStartDate(rs.getDate("startdate"));
			askoffDto.setEndDate(rs.getDate("enddate"));
			askoffDto.setDays(rs.getInt("days"));
			askoffDto.setReason(rs.getString("reason"));
			askoffDtos.add(askoffDto);
		}
		
		return askoffDtos;
	}

	@Override
	public boolean deleteEmpAskoff(String askoffDtoId) throws Exception {
		// TODO Auto-generated method stub
		boolean flag=false;
		String sql="DELETE FROM rcd_askoff WHERE id=?";
		Connection con=DBUtil.getConnection();
		PreparedStatement pst=con.prepareStatement(sql);
		pst.setString(1, askoffDtoId);
		int i=pst.executeUpdate();
		if(i>0){
			flag=true;
		}
		return flag;
	}
	public int selectAskoffByKey(KeysQueryDto dto, List<AskoffMD> pageData) throws Exception {
		// TODO Auto-generated method stub
		//查询总记录数
		int totalCount=0;
		Connection con =DBUtil.getConnection();
		StringBuffer whereSql=new StringBuffer();
		whereSql.append(" from rcd_askoff where 1=1 ");
		if (dto.getKeys().get("days")!=null) {
			whereSql.append(" and days =?");
		}
		if (dto.getKeys().get("startdate")!=null) {
			whereSql.append(" and startdate=?");
		}
		if (dto.getKeys().get("enddate")!=null) {
			whereSql.append(" and enddate=?");
		}
		if (dto.getKeys().get("reason")!=null) {
			String key=String.valueOf(dto.getKeys().get("reason"));
			key="%"+key+"%";
			whereSql.append(" and reason like ?");
		}
		PreparedStatement pst=con.prepareStatement("select count(*)"+whereSql.toString());
		int i=0;
		if (dto.getKeys().get("days")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("days")));
		}
		if (dto.getKeys().get("startdate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
		}
		if (dto.getKeys().get("enddate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
		}
		if (dto.getKeys().get("reason")!=null) {
			pst.setString(++i, (String)(dto.getKeys().get("reason")));
		}
		ResultSet rs=pst.executeQuery();
		if (rs.next()) {
			totalCount=rs.getInt(1);
		}
		rs.close();
		pst.close();
		
		//分页查询
		StringBuilder selectSql=new StringBuilder();
		selectSql.append("select * ").append(whereSql);
		selectSql.append(" limit ").append((dto.getPageNo()-1)*dto.getPageSize())
		.append(",").append(dto.getPageSize());
		pst=con.prepareStatement(selectSql.toString());
		i=0;
		if (dto.getKeys().get("days")!=null) {
			pst.setDouble(++i, (Integer)(dto.getKeys().get("days")));
		}
		if (dto.getKeys().get("startdate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("startdate")));
		}
		if (dto.getKeys().get("enddate")!=null) {
			pst.setDate(++i, (Date)(dto.getKeys().get("enddate")));
		}
		if (dto.getKeys().get("reason")!=null) {
			pst.setString(++i, (String)(dto.getKeys().get("reason")));
		}
		rs=pst.executeQuery();
		while (rs.next()) {
			AskoffMD as=new AskoffMD();
			EmployeeMD emp=new EmployeeMD();
			emp.setId(rs.getString("emp_id"));
			as.setId(rs.getString("id"));
			as.setReason(rs.getString("reason"));
			as.setDays(rs.getInt("days"));
			as.setStartDate(rs.getDate("startdate"));
			as.setEndDate(rs.getDate("enddate"));
			pageData.add(as);
		}
		return totalCount;
	}


}


